-- created by kumiler
-- on 2022/7/26 9:12
-- DESC 车件分离明细表DDL
-- modify v1.1 : jimmyk.ji
-- modify time : 2023-05-17
-- DESC 增加罚款配置相关维度

set spark.sql.crossJoin.enabled=true;
insert overwrite table jms_dm.dm_waybill_transfer_separated_unload_agg partition (dt = '{{ execution_date | date_add(-1) | cst_ds }}')
select t.date_time,
       t.ordersource_code,
       t.ordersource_name,
       t.goods_type_code,
       t.goods_type_name,
       t.unload_regional_id,
       t.unload_regional_desc,
       t.unload_agent_code,
       t.unload_agent_name,
       t.unload_franchisee_code,
       t.unload_franchisee_name,
       t.transfer_center,
       t.transfer_center_name,
       t.unload_center_cnt,
       t.diff_shipment_cnt,
       t.diff_shipment_operate_ratio,
       t.bill_separated_cnt,
       t.bill_separated_operate_ratio,
       round(diff_shipment_cnt * 100 / sum(diff_shipment_cnt) over (), 2)   as diff_shipment_ratio,
       round(bill_separated_cnt * 100 / sum(bill_separated_cnt) over (), 2) as bill_separated_ratio,
       date_format(`current_timestamp`(), 'yyyy-MM-dd HH:mm:ss')            as insert_etl_time
       ,b.fee_name
       ,b.fee_level
       ,b.fee_rate_level
       ,b.fee_per
       ,b.fee_maximum
       ,b.fee_minimum
       ,b.fee_update_time
       ,b.fee_update_by_name
       ,b.fee_update_by_code
from (
         select date_time,
                ordersource_code,
                ordersource_name,
                goods_type_code,
                goods_type_name,
                unload_regional_id,
                unload_regional_desc,
                unload_agent_code,
                unload_agent_name,
                unload_franchisee_code,
                unload_franchisee_name,
                transfer_center,
                transfer_center_name,
                max(billcode_center_cnt)                                        as unload_center_cnt,           -- 卸车中心到票数
                sum(nvl(diff_shipment, 0))                                      as diff_shipment_cnt,           -- 卸车中心任务号不一致票数
                round(sum(nvl(diff_shipment, 0)) * 100 / max(billcode_cnt), 5)  as diff_shipment_operate_ratio, -- 操作票是占比
                -- 卸车中心任务单不一致票数全网占比=任务单不一致票数/查询日期总任务单不一致数据*100.00%

                sum(nvl(bill_separated, 0))                                     as bill_separated_cnt,          -- 车件分离票数
                round(sum(nvl(bill_separated, 0)) * 100 / max(billcode_cnt), 5) as bill_separated_operate_ratio, -- 车件分离票数占比
                -- 车件分离票数占全网比=车件分离票数/当天总车件分离票数*100.00%
                dt
         from jms_dm.dm_waybill_transfer_separated_detail_dt
         where dt = '{{ execution_date | date_add(-1) | cst_ds }}'
         group by date_time,ordersource_code, ordersource_name,
                  goods_type_code, goods_type_name, unload_regional_id, unload_regional_desc,
                  unload_agent_code, unload_agent_name, unload_franchisee_code, unload_franchisee_name,
                  transfer_center, transfer_center_name,dt
     ) t
     left join (
          select  fee_name         as  fee_name
                 ,fee_level        as  fee_level
                 ,fee_rate_level   as  fee_rate_level
                 ,fee_per          as  fee_per
                 ,maximum          as  fee_maximum
                 ,minimum          as  fee_minimum
                 ,update_time      as  fee_update_time
                 ,update_by_name   as  fee_update_by_name
                 ,update_by_code   as  fee_update_by_code
                 ,'{{ execution_date | date_add(-1) | cst_ds }}' as dt_b
            from jms_dim.dim_tab_forfeit_config_new
           where fee_name ='车件分离罚款001'
		     and  fee_start_time <= '{{ execution_date | date_add(-1) | cst_ds }}'
             and fee_end_time >='{{ execution_date | date_add(-1) | cst_ds }}'
             and statue =1
             and is_delete = 1   --未删除
            order by fee_update_time desc limit 1
           ) b
     on t.dt = b.dt_b
distribute by 1;
